# -*- coding: utf-8 -*-
from statistics import median_high

import pymysql
import requests
import time
from datetime import datetime, timedelta
from flask import Flask, request, jsonify
from flask_cors import CORS  # 新增跨域支持
from threading import Lock, Thread, Semaphore
import schedule
import logging
import re
from typing import Dict, List, Optional
from concurrent.futures import ThreadPoolExecutor, as_completed
from flask_socketio import SocketIO, emit, disconnect

from push import Push

"""
修改后的版本：
1. 增加自增ID作为主键
2. 将gmt_bet_no改为唯一键
3. 确保open_number存入时去除逗号
"""
# ================= 初始化配置 =================
app = Flask(__name__)
CORS(app)
socketio = SocketIO(app, cors_allowed_origins="*")
user_sid_map = {}
user_sid_lock = Lock()  # 新增锁保证线程安全
  # 允许所有跨域请求

# MySQL数据库配置
MYSQL_CONFIG = {
    "host": "192.168.0.15",
    "user": "root",
    "port": 3306,
    "password": "abc123456.",
    "database": "lottery_data_func",
    "charset": "utf8mb4",
    "cursorclass": pymysql.cursors.DictCursor,
}

API_KEY = "gjtnfsrszh"  # eleven1:tpbimzqmwl, eleven:jpxstfcblx   hqhcihfopa
BASE_URL = "https://api.duoqu.online"
REQUEST_LOCK = Lock()

# 并发控制
MAX_WORKERS = 10  # 最大线程数
REQUEST_SEMAPHORE = Semaphore(60)  # 每分钟最多60次请求

# ================= 日志配置 =================
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s - %(levelname)s - %(message)s",
    handlers=[logging.FileHandler("lottery_service.log"), logging.StreamHandler()],
)
logger = logging.getLogger(__name__)


# ================= 数据库工具函数 =================
def sanitize_table_name(lottery_code: str) -> str:
    """将彩票代码转换为安全的表名"""
    table_name = re.sub(r"[^a-zA-Z0-9]", "_", lottery_code).lower()
    if not table_name[0].isalpha():
        table_name = "t_" + table_name
    return table_name


def create_lottery_table(connection, table_name: str):
    """创建彩种专用表（带自增ID主键）"""
    with connection.cursor() as cursor:
        cursor.execute(
            f"""
            CREATE TABLE IF NOT EXISTS `{table_name}` (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gmt_bet_no VARCHAR(50) NOT NULL,
                open_number VARCHAR(100) NOT NULL,
                open_time DATETIME NOT NULL,
                fetch_time DATETIME NOT NULL,
                UNIQUE KEY uk_gmt_bet_no (gmt_bet_no),
                INDEX idx_open_time (open_time)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """
        )
    connection.commit()
    logger.info(f"表 {table_name} 初始化完成")


def init_db():
    """初始化数据库和所有彩种表"""
    try:
        connection = pymysql.connect(**MYSQL_CONFIG)

        with connection.cursor() as cursor:
            cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS lottery_meta (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    lottery_code VARCHAR(50) NOT NULL UNIQUE,
                    lottery_name VARCHAR(100) NOT NULL,
                    table_name VARCHAR(100) NOT NULL UNIQUE,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    last_bet_no VARCHAR(50) COMMENT '最后记录的期号',
                    last_request_time DATETIME COMMENT '最后请求时间'
                )
            """
            )

        connection.commit()
        logger.info("数据库元表初始化完成")
    except Exception as e:
        logger.error(f"数据库初始化失败: {e}")
        raise
    finally:
        if connection:
            connection.close()


init_db()


# ================= 数据库连接助手 =================
def get_db_connection():
    """获取MySQL数据库连接"""
    return pymysql.connect(**MYSQL_CONFIG)


def get_last_bet_no(lottery_code: str) -> Optional[str]:
    """获取最后记录的期号"""
    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                """
                SELECT last_bet_no FROM lottery_meta
                WHERE lottery_code = %s
            """,
                (lottery_code,),
            )
            result = cursor.fetchone()
            return result["last_bet_no"] if result and result["last_bet_no"] else None
    finally:
        connection.close()


def update_last_bet_no(lottery_code: str, bet_no: str):
    """更新最后记录的期号"""
    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                """
                UPDATE lottery_meta
                SET last_bet_no = %s,
                    last_request_time = NOW()
                WHERE lottery_code = %s
            """,
                (bet_no, lottery_code),
            )
        connection.commit()
    finally:
        connection.close()


def format_open_number(open_number: str) -> str:
    """格式化开奖号码，去除逗号和空格"""
    if not open_number:
        return ""
    return open_number.replace(",", "").replace(" ", "")


# ================= 数据采集器 =================
class DataCollector:
    def __init__(self):
        self.active_lottery_codes = [
            "Ethereum1",
            "TRON1",
            "Binance1",
            "TRON1-Hash",
            "Binance-BTC",
        ]
        self.lottery_info_cache = {}  # 彩种信息缓存

    def _ensure_table_exists(self, lottery_code: str, lottery_name: str) -> str:
        """确保彩种表存在并返回表名"""
        table_name = sanitize_table_name(lottery_code)
        connection = get_db_connection()
        try:
            with connection.cursor() as cursor:
                cursor.execute(
                    """
                    SELECT table_name FROM lottery_meta 
                    WHERE lottery_code = %s
                """,
                    (lottery_code,),
                )
                result = cursor.fetchone()

                if not result:
                    create_lottery_table(connection, table_name)
                    cursor.execute(
                        """
                        INSERT INTO lottery_meta 
                        (lottery_code, lottery_name, table_name)
                        VALUES (%s, %s, %s)
                    """,
                        (lottery_code, lottery_name, table_name),
                    )
                    connection.commit()
                else:
                    table_name = result["table_name"]

            return table_name
        finally:
            connection.close()

    def _save_data_to_db(self, data: Dict) -> int:
        """保存数据到对应彩种表（带ID主键）"""
        lottery_code = data["lotteryCode"]
        lottery_name = data["lotteryName"]
        table_name = self._ensure_table_exists(lottery_code, lottery_name)

        last_bet_no = get_last_bet_no(lottery_code)

        saved_count = 0
        fetch_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        connection = get_db_connection()

        try:
            with connection.cursor() as cursor:
                # 获取API返回数据中的最新期号
                api_latest_bet_no = None
                if data["data"]:
                    api_latest_bet_no = max(
                        int(item["gmtBetNo"])
                        for item in data["data"]
                        if item.get("gmtBetNo", "").isdigit()
                    )

                # 如果数据库中没有记录或者API有更新的数据
                if last_bet_no is None or (
                    api_latest_bet_no is not None
                    and int(api_latest_bet_no) > int(last_bet_no)
                ):
                    new_data = []
                    for item in data["data"]:
                        current_bet_no = item.get("gmtBetNo", "")
                        if not current_bet_no:
                            continue

                        # 如果是第一次获取数据或者比上次记录的期号大
                        if last_bet_no is None or int(current_bet_no) > int(
                            last_bet_no
                        ):
                            new_data.append(item)

                    if not new_data:
                        logger.info(f"没有新数据需要保存 for {lottery_code}")
                        return 0

                    # 按开奖时间升序排列
                    new_data.sort(
                        key=lambda x: datetime.strptime(
                            x["openTime"], "%Y-%m-%d %H:%M:%S"
                        )
                    )

                    max_bet_no = None  # 记录最大的期号
                    for item in new_data:
                        try:
                            # 处理open_number，去除逗号
                            clean_open_number = item.get("openNumber", "").replace(
                                ",", ""
                            )

                            cursor.execute(
                                f"""
                                INSERT INTO `{table_name}` (
                                    gmt_bet_no, open_number, 
                                    open_time, fetch_time
                                ) VALUES (%s, %s, %s, %s)
                                ON DUPLICATE KEY UPDATE 
                                    open_number = VALUES(open_number),
                                    fetch_time = VALUES(fetch_time)
                            """,
                                (
                                    item.get("gmtBetNo", ""),
                                    clean_open_number,
                                    item.get("openTime", ""),
                                    fetch_time,
                                ),
                            )
                            if cursor.rowcount == 1:
                                saved_count += 1
                                logger.info(
                                    f"新增号码 - 彩种: {lottery_name}({lottery_code}), "
                                    f"期号: {item.get('gmtBetNo', '')}, "
                                    f"号码: {clean_open_number}, "
                                    f"开奖时间: {item.get('openTime', '')}"
                                )
                                current_bet_no = int(item.get("gmtBetNo", "0"))
                                if max_bet_no is None or current_bet_no > max_bet_no:
                                    max_bet_no = current_bet_no

                                try:
                                    push.push_data(**{
                                        "issue": item.get("gmtBetNo", ""),
                                        "numbers": item.get("openNumber", ""),
                                        "lottery": lottery_code,
                                        "lotteryType": "P3D",
                                        "openTime": item.get("openTime", ""),
                                    })
                                except Exception as e:
                                    logger.error(f"推送数据失败: {e}")

                        except Exception as e:
                            logger.error(f"插入数据失败: {e}")

                    connection.commit()
                    if saved_count > 0:
                        logger.info(f"保存 {saved_count} 条新数据到 {lottery_code} 表")
                        if max_bet_no is not None:
                            update_last_bet_no(lottery_code, str(max_bet_no))
                    return saved_count
                else:
                    logger.info(f"没有发现比 {last_bet_no} 更新的数据")
                    return 0
        finally:
            connection.close()

    def fetch_lottery_data(self, lottery_code: str) -> Optional[Dict]:
        """获取单个彩种数据"""
        with REQUEST_SEMAPHORE:
            try:
                url = f"{BASE_URL}/data?code={lottery_code}&limit=5&key={API_KEY}&sort=desc"
                logger.debug(f"请求API: {url.split('key=')[0]}...")

                response = requests.get(url, timeout=10)
                response.raise_for_status()
                data = response.json()

                if data.get("info") != "success" or not data.get("data"):
                    logger.error(f"API返回错误: {data.get('info')}")
                    return None

                data["data"].sort(
                    key=lambda x: datetime.strptime(x["openTime"], "%Y-%m-%d %H:%M:%S"),
                    reverse=True,
                )

                self.lottery_info_cache[lottery_code] = {
                    "lotteryName": data.get("lotteryName", ""),
                    "lotteryCode": lottery_code,
                }

                return data

            except Exception as e:
                logger.error(f"采集 {lottery_code} 失败: {e}")
                return None

    def fetch_and_save(self, lottery_code: str) -> bool:
        """获取并保存单个彩种数据"""
        data = self.fetch_lottery_data(lottery_code)
        if not data:
            return False

        saved_count = self._save_data_to_db(data)
        return saved_count > 0

    def concurrent_collect(self):
        """并发采集所有彩种数据"""
        logger.info("开始并发采集任务...")
        start_time = time.time()

        with ThreadPoolExecutor(max_workers=MAX_WORKERS) as executor:
            futures = {
                executor.submit(self.fetch_and_save, code): code
                for code in self.active_lottery_codes
            }

            for future in as_completed(futures):
                code = futures[future]
                try:
                    success = future.result()
                    logger.info(f"彩种 {code} 采集{'成功' if success else '失败'}")
                except Exception as e:
                    logger.error(f"彩种 {code} 采集出错: {e}")

        elapsed = time.time() - start_time
        logger.info(f"并发采集完成，耗时 {elapsed:.2f} 秒")


# ================= API服务 =================
@app.route("/data", methods=["GET"])
def query_data():
    """查询接口（返回简化后的数据结构）"""
    lottery_code = request.args.get("code", "Ethereum1-Cash5")
    limit = min(int(request.args.get("limit", 1)), 20000)
    date = request.args.get("date")

    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                """
                SELECT table_name, lottery_name FROM lottery_meta
                WHERE lottery_code = %s
            """,
                (lottery_code,),
            )
            meta = cursor.fetchone()

            if not meta:
                return (
                    jsonify(
                        {
                            "info": "lottery code not found",
                            "lotteryCode": lottery_code,
                        }
                    ),
                    404,
                )

            query = f"""
                SELECT gmt_bet_no, open_number
                FROM `{meta['table_name']}`
                WHERE 1=1
            """
            params = []

            if date:
                query += " AND DATE(open_time) = %s"
                params.append(date)

            query += " ORDER BY open_time DESC LIMIT %s"
            params.append(limit)

            cursor.execute(query, params)
            results = cursor.fetchall()

            if not results:
                return jsonify(
                    {
                        "info": "success",
                        "lotteryCode": lottery_code,
                        "lotteryName": meta["lottery_name"],
                        "data": [],
                    }
                )

            formatted_data = [
                {
                    "gmtBetNo": row["gmt_bet_no"],
                    "openNumber": format_open_number(row["open_number"]),
                }
                for row in results
            ]

            return jsonify(
                {
                    "info": "success",
                    "lotteryCode": lottery_code,
                    "lotteryName": meta["lottery_name"],
                    "data": formatted_data,
                }
            )
    except Exception as e:
        logger.error(f"查询失败: {e}")
        return jsonify({"info": str(e)}), 500
    finally:
        connection.close()


@app.route("/data_simple", methods=["GET"])  
def query_data_simple():
    """简化查询接口（返回纯字符串格式）"""
    lottery_code = request.args.get("code", "Ethereum1-Cash5")
    limit = min(int(request.args.get("limit", 1)), 2000)
    date = request.args.get("date")

    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                """
                SELECT table_name FROM lottery_meta
                WHERE lottery_code = %s
            """,
                (lottery_code,),
            )
            meta = cursor.fetchone()

            if not meta:
                return "lottery code not found", 404

            query = f"""
                SELECT gmt_bet_no, open_number
                FROM `{meta['table_name']}`
                WHERE 1=1
            """
            params = []

            if date:
                query += " AND DATE(open_time) = %s"
                params.append(date)

            query += " ORDER BY open_time DESC LIMIT %s"
            params.append(limit)

            cursor.execute(query, params)
            results = cursor.fetchall()

            if not results:
                return ""

            formatted_data = [
                f"{row['gmt_bet_no']},{format_open_number(row['open_number'])}"
                for row in results
            ]

            return "^".join(formatted_data)

    except Exception as e:
        logger.error(f"查询失败: {e}")
        return "error", 500
    finally:
        connection.close()


@app.route("/query", methods=["GET", "POST"])
def query_by_betno():
    """查询大于指定期号的数据"""
    if request.method == "GET":
        lottery_code = request.args.get("code")
        gmt_bet_no = request.args.get("gmtBetNo")
    else:
        data = request.get_json() or {}
        lottery_code = data.get("code")
        gmt_bet_no = data.get("gmtBetNo")

    if not lottery_code or not gmt_bet_no:
        return (
            jsonify(
                {"info": "invalid request", "message": "必须提供code和gmtBetNo参数"}
            ),
            400,
        )

    if not gmt_bet_no.isdigit():
        return jsonify({"info": "invalid gmtBetNo", "message": "期号必须是数字"}), 400

    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                """
                SELECT table_name, lottery_name FROM lottery_meta
                WHERE lottery_code = %s
            """,
                (lottery_code,),
            )
            meta = cursor.fetchone()

            if not meta:
                return (
                    jsonify(
                        {"info": "not found", "message": f"未找到彩种 {lottery_code}"}
                    ),
                    404,
                )

            cursor.execute(
                f"""
                SELECT gmt_bet_no, open_number
                FROM `{meta['table_name']}`
                WHERE CAST(gmt_bet_no AS UNSIGNED) > %s
                ORDER BY CAST(gmt_bet_no AS UNSIGNED) ASC
            """,
                (int(gmt_bet_no),),
            )

            results = cursor.fetchall()

            return jsonify(
                {
                    "info": "success",
                    "lotteryCode": lottery_code,
                    "lotteryName": meta["lottery_name"],
                    "minBetNo": gmt_bet_no,
                    "count": len(results),
                    "data": [
                        {
                            "gmtBetNo": row["gmt_bet_no"],
                            "openNumber": format_open_number(row["open_number"]),
                        }
                        for row in results
                    ],
                }
            )

    except ValueError:
        return jsonify({"info": "error", "message": "期号必须是有效数字"}), 400
    except Exception as e:
        logger.error(f"查询失败: {str(e)}")
        return jsonify({"info": "error", "message": "服务器内部错误"}), 500
    finally:
        connection.close()


@app.route("/query_simple", methods=["GET", "POST"])
def query_by_betno_simple():
    """简化字符串格式的期号查询接口"""
    if request.method == "GET":
        lottery_code = request.args.get("code")
        gmt_bet_no = request.args.get("gmtBetNo")
    else:
        data = request.get_json() or {}
        lottery_code = data.get("code")
        gmt_bet_no = data.get("gmtBetNo")

    if not lottery_code or not gmt_bet_no:
        return "invalid_params", 400

    if not gmt_bet_no.isdigit():
        return "invalid_betno", 400

    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                """
                SELECT table_name FROM lottery_meta
                WHERE lottery_code = %s
            """,
                (lottery_code,),
            )
            if not cursor.fetchone():
                return "lottery_not_found", 404

            cursor.execute(
                f"""
                SELECT gmt_bet_no, open_number
                FROM `{sanitize_table_name(lottery_code)}`
                WHERE CAST(gmt_bet_no AS UNSIGNED) > %s
                ORDER BY CAST(gmt_bet_no AS UNSIGNED) ASC
            """,
                (int(gmt_bet_no),),
            )

            results = [
                f"{row['gmt_bet_no']},{format_open_number(row['open_number'])}"
                for row in cursor.fetchall()
            ]

            return "^".join(results) if results else "no_new_data"

    except Exception as e:
        logger.error(f"简化查询失败: {e}")
        return "server_error", 500
    finally:
        conn.close()

@app.route("/test", methods=["GET"])
def test_api():
    send_message_to_user("qqq", "1111111111")
    return "1"


# ws://192.168.0.254:18765/ws/wzt888
@socketio.on('connect')
def handle_connect():
    username = request.args.get('username')
    if username:
        with user_sid_lock:
            # 覆盖旧连接，保证最新sid
            user_sid_map[username] = request.sid
        logger.info(f"用户 {username} 连接，SID: {request.sid}")
    else:
        disconnect()

@socketio.on('disconnect')
def handle_disconnect():
    with user_sid_lock:
        # 清理断开的sid
        for user, sid in list(user_sid_map.items()):
            if sid == request.sid:
                del user_sid_map[user]
                logger.info(f"用户 {user} 已断开连接")
                break

@socketio.on('heartbeat')
def handle_heartbeat():
    # 客户端定期发送心跳包，服务端可用于活跃检测
    emit('heartbeat_ack')
    logger.debug(f"收到心跳: {request.sid}")



def send_message_to_user(username, message):
    with user_sid_lock:
        sid = user_sid_map.get(username)
    if sid:
        print("发送啊1111")
        try:
            socketio.emit('message', {'msg': message}, room=sid)
        except Exception as e:
            # 推送失败自动清理失效sid
            print("失败了吗")
            with user_sid_lock:
                if username in user_sid_map and user_sid_map[username] == sid:
                    del user_sid_map[username]
            logger.error(f"推送到 {username} 失败，sid已清理: {e}")


# ================= 定时任务 =================
collector = DataCollector()
push = Push()


def scheduled_job():
    """每分钟执行一次的全量采集"""
    try:
        collector.concurrent_collect()
    except Exception as e:
        logger.error(f"定时任务异常: {e}")


def incremental_check():
    """每秒执行一次的增量检查"""
    try:
        for code in collector.active_lottery_codes:
            try:
                collector.fetch_and_save(code)
            except Exception as e:
                logger.error(f"增量检查 {code} 出错: {e}")
    except Exception as e:
        logger.error(f"增量检查异常: {e}")


def run_scheduler():
    """运行调度任务"""
    schedule.every(1).minutes.do(scheduled_job)
    schedule.every(1).seconds.do(incremental_check)

    while True:
        schedule.run_pending()
        time.sleep(0.1)


# ================= 主程序 =================
if __name__ == "__main__":
    init_db()
    # Thread(target=collector.concurrent_collect).start()
    # Thread(target=run_scheduler, daemon=True).start()
    # app.run(host="0.0.0.0", port=9998, threaded=True)
    socketio.run(app, host="0.0.0.0", port=9998)
